﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Data.SqlClient;
using System.Diagnostics;
using System.EnterpriseServices;
using System.Linq;
using System.Web.Mvc;
using System.Web.Script.Serialization;
using BLL;
using Common;
using Model;
using System.Text;
using System.Web;
using System.Net;
using System.Data.OleDb;
using System.Collections;
using System.Data.OracleClient;
using Npgsql;


namespace WebPortal.Controllers
{
    public class GetDateController : BaseController
    {

        private static EncrypCode encrypCode = new EncrypCode();

        /// <summary>
        /// 读取源系统表数据
        /// </summary>
        /// <param name="systemID"></param>
        /// <param name="viewName"></param>
        /// <param name="dbName"></param>
        /// <returns></returns> 
        public ActionResult GetSourceTableData(string systemID, string viewName, string dbName)
        {
            JsonResponse<string> jsonResponse = new JsonResponse<string>();
            try
            {
                jsonResponse = CallGetSourceDate(systemID, viewName, dbName);
            }
            catch (Exception ex)
            {
                jsonResponse.Success = false;
                jsonResponse.Message = ex.Message;
            }
            return Json(jsonResponse);
        }

        private JsonResponse<string> CallGetSourceDate(string systemID, string viewName, string dbName)
        {
            JsonResponse<string> jsonResponse = new JsonResponse<string>();
            Dictionary<String, Object> jsonDict = new Dictionary<String, Object>();
            try
            {
                SystemConfigurationController sourceCon = new SystemConfigurationController();
                #region 参数配置
                if (!string.IsNullOrEmpty(viewName) && viewName.IndexOf("~") > -1)
                    viewName = viewName.Replace("~", "\"");
                string order = Request.Params["order"];
                string sort = Request.Params["sort"];
                string viewID = Request.Params["viewID"];
                string fileName = Request.Params["fileName"];
                string DBID = Request.Params["DBID"];
                string query = Request.Params["query"];
                List<string> sql = new List<string>();
                IDataParameter[] para = null;
                IDataParameter[] sqlPara = null;
                IDataParameter[] oraPara = null;
                IDataParameter[] myPara = null;
                IDataParameter[] oledbPra = null;
                IDataParameter[] NpgPra = null;
                if (!string.IsNullOrEmpty(query) && query != "[]") //解析搜索条件
                {
                    query = query.Replace("\"", "").Replace("[[", "").Replace("]]", "");
                    IEnumerable<string> arrList = query.Split("],[");

                    sqlPara = new IDataParameter[arrList.Count()];
                    oraPara = new IDataParameter[arrList.Count()];
                    myPara = new IDataParameter[arrList.Count()];
                    NpgPra = new IDataParameter[arrList.Count()];
                    oledbPra = new IDataParameter[arrList.Count()];
                    int i = 0;
                    foreach (string str in arrList)
                    {
                        string[] item = str.Split(',');
                        switch (item[1])
                        {
                            case "like":
                            case "not like":
                                sql.Add(string.Format(" {0} {1} '%'+@{0}+'%' ", item[0], item[1]));
                                break;
                            case "is null":
                            case "is NOT null":
                                sql.Add(string.Format(" {0} {1} ", item[0], item[1]));
                                break;
                            case "in":
                            case "not in":
                                sql.Add(string.Format(" {0} {1} (@{0}) ", item[0], item[1]));
                                break;
                            default:
                                sql.Add(string.Format(" {0} {1} @{0} ", item[0], item[1]));// " column=@column"
                                break;
                        }
                        sqlPara[i] = new SqlParameter("@" + item[0], item[2]);
                        object paraValue = new object();
                        if (!string.IsNullOrEmpty(item[2]))
                        {
                            switch (item[3])
                            {
                                case "Int32":
                                    paraValue = Convert.ToInt32(item[2]);
                                    break;
                                case "Double":
                                    paraValue = Convert.ToDouble(item[2]);
                                    break;
                                case "DateTime":
                                    paraValue = Convert.ToDateTime(item[2]);
                                    break;
                                default:
                                    paraValue = item[2];
                                    break;
                            }
                        }
                        else
                            paraValue = "";
                        oraPara[i] = new Oracle.ManagedDataAccess.Client.OracleParameter(":" + item[0], paraValue);
                        NpgPra[i] = new NpgsqlParameter(":" + item[0], paraValue);
                        myPara[i] = new MySql.Data.MySqlClient.MySqlParameter("?" + item[0], paraValue);
                        oledbPra[i] = new OleDbParameter("@" + item[0], paraValue);
                        i++;
                    }
                }

                if (string.IsNullOrEmpty(sort))
                    sort = "desc";
                if (string.IsNullOrEmpty(order))
                    order = "";
                #endregion
                LoginInfo loginInfos = base.loginInfo();
                loginInfos.DBName = dbName;
                EtlSourceSystemBLL sourceSystemBLL = new EtlSourceSystemBLL(loginInfos);
                EtlSourceViewConfigBLL sourceViewConfigBLL = new EtlSourceViewConfigBLL(loginInfos);
                ETL_Source_ViewConfigInfo sourceModel = sourceViewConfigBLL.GetSourceViewConfigInfoByGuid(viewID);
                if (!string.IsNullOrEmpty(viewID))//已配置数据源表
                {
                    viewName = sourceModel.ViewName;
                    if (string.IsNullOrEmpty(DBID))
                        DBID = sourceModel.DBID;
                }

                var html = new System.Text.StringBuilder();

                string sourceID = systemID;
                ETLDbConfig dbConfig = sourceSystemBLL.GetDbConfigBySysID(systemID)[0];

                string connstr = "";
                string connType = "";

                if (!string.IsNullOrEmpty(DBID))
                    dbConfig = sourceSystemBLL.GetDbConfigByID(DBID);
                connstr = encrypCode.Decrypto(dbConfig.DbConnectionStr);//源系统数据库连接字符串
                connType = dbConfig.DbType.ToString();//数据库类型
                #region 读取数据
                if (!string.IsNullOrEmpty(connstr))
                {
                    if (connType == "6" || connType == "7")//平面文件
                    {
                        #region 平面文件
                        connstr = dbConfig.DbDataSource;
                        List<string> fileList = new List<string>();
                        bool read = false;
                        if (dbConfig.DbDataSource.StartsWith("ftp"))
                            SysPublicBLL.readerFtp(dbConfig.DbDataSource, dbConfig.DbUserID, encrypCode.Decrypto(dbConfig.DbPassword), out fileList);
                        else
                        {
                            sourceCon.GetFileNames(dbConfig.DbDataSource, connType == "6" ? ".txt" : ".csv", out fileList);
                        }
                        string wdc = viewName.Replace("*", "").ToLower();
                        foreach (string s in fileList)
                        {
                            if (s.ToLower().StartsWith(wdc))
                            {
                                viewName = s;
                                read = true;
                                break;
                            }
                        }

                        if (read)
                        {
                            jsonResponse.Message = string.Join("$", fileList);
                            jsonResponse.Data = sourceCon.ReadTxtData(connstr, viewName, dbConfig.DbUserID, encrypCode.Decrypto(dbConfig.DbPassword), 0, 0, "\\r\\n", "", ",");
                        }
                        else
                        {
                            jsonResponse.Message = "读取失败！找不到指定文件。";
                            jsonResponse.Success = false;
                        }
                        #endregion
                    }
                    else
                    {
                        #region 其他数据类型
                        switch (connType)
                        {
                            case "3":
                            case "5":
                                connstr = dbConfig.DbDataSource;
                                if (connstr.StartsWith("ftp"))
                                {
                                    if (connType == "3")
                                    {
                                        connstr = "/files/" + systemID.Replace("-", "") + "/" + dbConfig.DbDataSource.Substring(dbConfig.DbDataSource.LastIndexOf("/") + 1);
                                        connstr = Server.MapPath(connstr);
                                    }
                                    else
                                    {
                                        if (string.IsNullOrEmpty(fileName))
                                            fileName = sourceModel.ViewChnName.Substring(sourceModel.ViewChnName.LastIndexOf("/") + 1);
                                        else
                                            fileName = fileName.Substring(fileName.LastIndexOf("/") + 1);

                                        connstr = string.Format("/files/{0}/{1}", sourceID.Replace("-", ""), fileName);
                                        connstr = Server.MapPath(connstr);
                                    }
                                }
                                else if (connType == "5")
                                {
                                    if (string.IsNullOrEmpty(fileName))
                                        fileName = sourceModel.ViewChnName.Substring(sourceModel.ViewChnName.LastIndexOf("\\") + 1);
                                    else
                                        fileName = fileName.Substring(fileName.LastIndexOf("\\") + 1);
                                    connstr = connstr + "\\" + fileName;
                                }
                                para = oledbPra;
                                break;
                            case "2":
                                para = oraPara;
                                break;
                            case "4":
                                para = myPara;
                                break;
                            case "8":
                                para = NpgPra;
                                break;
                            default:
                                para = sqlPara;
                                break;
                        }
                        #endregion
                        DataTable dt = sourceSystemBLL.GetSourceTableData(viewName, connstr, connType, order, sort, sql.ToArray(), para);
                        jsonResponse.Data = sourceCon.DataTableToString(dt, 0, order, sort, true);
                        jsonResponse.Head = sourceCon.GetSourceTableHeadData(dt.Columns);
                    }
                    jsonResponse.Success = true;
                    jsonResponse.Message = "OK";
                }
                else
                {
                    jsonResponse.Success = false;
                    jsonResponse.Message = "空的数据库连接，无法读取数据";
                    jsonResponse.Data = "";
                }
                return jsonResponse;
                #endregion
            }
            catch (Exception ex)
            {
                jsonResponse.Success = false;
                jsonResponse.Message = "信息获取失败！" + ex.Message;
                jsonResponse.Data = "";
                return jsonResponse;
            }
        }
    }
}
